package module;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * Servlet implementation class NotificationServlet
 */
public class NotificationServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public NotificationServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		HttpSession session = request.getSession();
		RequestDispatcher dis;
		
		//Si je ne suis pas connect� alors je suis redirig�
		Integer membre = (Integer)session.getAttribute("user_id");
		if(membre == null){
			dis = request.getRequestDispatcher("/login.jsp");
			dis.forward(request, response);
			return;
		}
		
			 //Donn�es correctes, envoie de la requ�te � la base de donn�es
			try{
				Connection con;
				Context namingContext;
				PreparedStatement pstmt;
				
				namingContext = new InitialContext();
				DataSource dataSource = (DataSource)namingContext.lookup("java:comp/env/jdbc/MiniIBay");
				con = dataSource.getConnection();
				
				pstmt = con.prepareStatement("SELECT COUNT(*) as nb_notifs FROM message_vente mv, vente v, annonce a WHERE mv.id_vente = v.id_vente AND (v.id_acheteur = ? OR (v.id_annonce = a.id_annonce AND a.id_vendeur = ?)) AND mv.id_message NOT IN (SELECT id_message FROM message_vente_vu WHERE id_membre = ?)");
				pstmt.setInt(1, membre);
				pstmt.setInt(2, membre);
				pstmt.setInt(3, membre);
				
				ResultSet rs  = pstmt.executeQuery();
				rs.next();
				
				Integer nbNotifs = rs.getInt("nb_notifs");
				rs.close();
				
				pstmt = con.prepareStatement("SELECT COUNT(*) as nb_notifs FROM message_annonce ma WHERE ma.id_annonce IN (SELECT DISTINCT ma1.id_annonce FROM message_annonce ma1, annonce a WHERE ma1.id_membre = ? OR (a.id_annonce = ma1.id_annonce AND a.id_vendeur = ?)) AND ma.id_message NOT IN (SELECT mav.id_message FROM message_annonce_vu mav WHERE mav.id_membre = ?)");
				pstmt.setInt(1, membre);
				pstmt.setInt(2, membre);
				pstmt.setInt(3, membre);
				
				rs  = pstmt.executeQuery();
				rs.next();
				nbNotifs += rs.getInt("nb_notifs");
				rs.close();
				
				con.close();
				if(nbNotifs > 0)
					out.println("<a href=\"listenotifs\">"+nbNotifs+" notifications</a>");
				else
					out.println("<a href=\"listenotifs\">notifications</a>");
			}catch(NamingException e){
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}
